﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using bdxyit.com.RecruitmentAppSys.Models;
using System.Data.SqlClient;
using System.Data;
using bdxyit.com.RecruitmentAppSys.DBUtility;

/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：PostActionModels
*系统时间：3/25/2012 3:36:19 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012 3/25/2012 3:36:19 PM
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class PostActionDAL : IPostActionDAL
    {
        private const string SQL_SELECT_POSTACTIONS = " select    ActionID,PostID,CompanyID,Flg,StartTime,EndTime,Number from PostActions ";
        private const string SQL_SELECT_POSTACTIONS_BY_ACTIONID = " select  top 1 ActionID,PostID,CompanyID,Flg,StartTime,EndTime,Number from PostActions "
                                                                + "  where ActionID=@ActionID";
        private const string SQL_DELETE_POSTACTIONS_BY_ACTIONID = " delete from PostActions "
                                                                + "  where ActionID=@ActionID";
        private const string SQL_UPDATE_POSTACTIONS_BY_ACTIONID = " update PostActions set "
                                                                + " ActionID=@ActionID,"
                                                                + " PostID=@PostID,"
                                                                + " CompanyID=@CompanyID,"
                                                                + " Flg=@Flg,"
                                                                + " StartTime=@StartTime,"
                                                                + " EndTime=@EndTime"
                                                                + " Number=@Number"
                                                                + "  where ActionID=@ActionID";
        private const string SQL_INSERT_POSTACTIONS = " insert into PostActions("
                                                    + " ActionID,PostID,CompanyID,Flg,StartTime,EndTime,Number)"
                                                    + "  values ("
                                                    + " @ActionID,@PostID,@CompanyID,@Flg,@StartTime,@EndTime,@Number)";

        private const string FIELD_ActionID = "@ActionID";
        private const string FIELD_PostID = "@PostID";
        private const string FIELD_CompanyID = "@CompanyID";
        private const string FIELD_Flg = "@Flg";
        private const string FIELD_StartTime = "@StartTime";
        private const string FIELD_EndTime = "@EndTime";
        private const string FIELD_Number = "@Number";
        public bool InsertPostAction(PostAction cmodel)
        {
            bool flag = false;
            SqlParameter[] PostActionParms = GetPostActionParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句
            PostActionParms[0].Value = cmodel.ActionID;
            PostActionParms[1].Value = cmodel.PostID;
            PostActionParms[2].Value = cmodel.CompanyID;
            PostActionParms[3].Value = cmodel.Flg;
            PostActionParms[4].Value = cmodel.StartTime;
            PostActionParms[5].Value = cmodel.EndTime;
            PostActionParms[6].Value = cmodel.Number;
            foreach (SqlParameter parm in PostActionParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_POSTACTIONS;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public bool DeletePostAction(string ActionID)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_ActionID, SqlDbType.Int, 4);
            parm.Value = ActionID;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_POSTACTIONS_BY_ACTIONID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public bool UpdatePostAction(PostAction cmodel)
        {
            bool flag = false;
            SqlParameter[] PostActionParms = GetPostActionParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句
            PostActionParms[0].Value = cmodel.ActionID;
            PostActionParms[1].Value = cmodel.PostID;
            PostActionParms[2].Value = cmodel.CompanyID;
            PostActionParms[3].Value = cmodel.Flg;
            PostActionParms[4].Value = cmodel.StartTime;
            PostActionParms[5].Value = cmodel.EndTime;
            PostActionParms[6].Value = cmodel.Number;
            foreach (SqlParameter parm in PostActionParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_POSTACTIONS_BY_ACTIONID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public PostAction SelectPostActionByActionID(string ActionID)
        {
            PostAction cmodel = null;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_ActionID, SqlDbType.Int, 4);
            parm.Value = ActionID;
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_POSTACTIONS_BY_ACTIONID, parm))
            {
                if (dr.Read())
                {
                    cmodel = new PostAction();
                    if (dr.HasRows == true)
                    {
                        if (dr["ActionID"] != null && dr["ActionID"].ToString() != "")
                        {
                            cmodel.ActionID = int.Parse(dr["ActionID"].ToString());
                        }
                        if (dr["PostID"] != null && dr["PostID"].ToString() != "")
                        {
                            cmodel.PostID = dr["PostID"].ToString();
                        }
                        if (dr["CompanyID"] != null && dr["CompanyID"].ToString() != "")
                        {
                            cmodel.CompanyID = dr["CompanyID"].ToString();
                        }
                        if (dr["Flg"] != null && dr["Flg"].ToString() != "")
                        {
                            cmodel.Flg = int.Parse(dr["Flg"].ToString());
                        }
                        if (dr["StartTime"] != null && dr["StartTime"].ToString() != "")
                        {
                            cmodel.StartTime = DateTime.Parse(dr["StartTime"].ToString());
                        }
                        if (dr["EndTime"] != null && dr["EndTime"].ToString() != "")
                        {
                            cmodel.EndTime = DateTime.Parse(dr["EndTime"].ToString());
                        }
                        if (dr["Number"] != null && dr["Number"].ToString() != "")
                        {
                            cmodel.Number = int.Parse(dr["Number"].ToString());
                        }
                    }
                }
            }
            return cmodel;
        }
        public List<PostAction> SelectPostAction()
        {
            List<PostAction> selectlist = new List<PostAction>();
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_POSTACTIONS))
            {
                while (dr.Read())
                {
                    PostAction cmodel = new PostAction();
                    //写添赋值语句
                    if (dr["ActionID"] != null && dr["ActionID"].ToString() != "")
                    {
                        cmodel.ActionID = int.Parse(dr["ActionID"].ToString());
                    }
                    if (dr["PostID"] != null && dr["PostID"].ToString() != "")
                    {
                        cmodel.PostID = dr["PostID"].ToString();
                    }
                    if (dr["CompanyID"] != null && dr["CompanyID"].ToString() != "")
                    {
                        cmodel.CompanyID = dr["CompanyID"].ToString();
                    }
                    if (dr["Flg"] != null && dr["Flg"].ToString() != "")
                    {
                        cmodel.Flg = int.Parse(dr["Flg"].ToString());
                    }
                    if (dr["StartTime"] != null && dr["StartTime"].ToString() != "")
                    {
                        cmodel.StartTime = DateTime.Parse(dr["StartTime"].ToString());
                    }
                    if (dr["EndTime"] != null && dr["EndTime"].ToString() != "")
                    {
                        cmodel.EndTime = DateTime.Parse(dr["EndTime"].ToString());
                    } if (dr["Number"] != null && dr["Number"].ToString() != "")
                    {
                        cmodel.Number = int.Parse(dr["Number"].ToString());
                    }

                    selectlist.Add(cmodel);
                }
            }
            return selectlist;
        }
        private SqlParameter[] GetPostActionParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter(FIELD_ActionID, SqlDbType.Int,4),
					new SqlParameter(FIELD_PostID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_CompanyID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_Flg, SqlDbType.Int,4),
					new SqlParameter(FIELD_StartTime, SqlDbType.DateTime),
					new SqlParameter(FIELD_EndTime, SqlDbType.DateTime),
            		new SqlParameter(FIELD_Number, SqlDbType.Int,4)};
            return parms;
        }

    }
}